package dto;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import model.Producer;

public class DtoProducer {
	private static List<Producer> getAll(DataManager dataManager,
			String sqlQuery) {
		List<Producer> list = new ArrayList<Producer>();
		Connection con = dataManager.getConnection();
		ResultSet rs = null;
		Statement statement = null;
		try {
			statement = con.createStatement();
			rs = statement.executeQuery(sqlQuery);
			while (rs.next()) {
				Producer producer = new Producer();
				producer.setProducerId(rs.getInt(1));
				producer.setProducerName(rs.getString(2));
				list.add(producer);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (statement != null)
					statement.close();
				dataManager.closeConnection(con);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return list;
	}

	public static List<Producer> getAllProducer(DataManager dataManager) {
		String sqlQuery = "select * from producer";

		return getAll(dataManager, sqlQuery);
	}

	public static Producer getProducerWithProducerId(DataManager dataManager,
			int producerId) {
		String sqlQuery = "select * from producer where producerId="
				+ producerId;

		List<Producer> producers = getAll(dataManager, sqlQuery);
		if (producers.size() == 0)
			return null;
		return producers.get(0);
	}

	public static List<Producer> getProducerWithCategoryId(
			DataManager dataManager, int categoryId) {
		String sqlQuery = "select Producer.producerId, Producer.producerName from Producer, ProducerOwnCategory where "
				+ "ProducerOwnCategory.categoryId = "
				+ categoryId
				+ " and Producer.producerId = "
				+ "ProducerOwnCategory.producerId";
		
		return getAll(dataManager, sqlQuery);
	}

	private static void excuteWithCategoryNonQuery(DataManager dataManager,
			String sqlQuery) {
		Connection con = dataManager.getConnection();
		Statement statement = null;

		try {
			statement = con.createStatement();
			statement.executeUpdate(sqlQuery);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (statement != null)
					statement.close();
				dataManager.closeConnection(con);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public static void updateProducerWithProducerId(int producerId,
			String producerName, DataManager dataManager) {
		String sqlQuery = "update Producer set producerName=\'" + producerName
				+ "\' where producerId=" + producerId;

		excuteWithCategoryNonQuery(dataManager, sqlQuery);
	}

	public static void deleteCategory(DataManager dataManager, int producerId) {
		String sqlQuery = "delete from Producer where producerId=" + producerId;

		excuteWithCategoryNonQuery(dataManager, sqlQuery);
	}

	public static void addCategory(DataManager dataManager, String producerName) {
		String sqlQuery = "insert into Producer (producerName) values ('"
				+ producerName + "')";

		excuteWithCategoryNonQuery(dataManager, sqlQuery);
	}
}
